Skip to content

Instantly share code, notes, and snippets.

@techphoria414
Last active September 9, 2016 01:51
Show Gist options
  • Save techphoria414/c2c561b231f8c4f080ac80962085108f to your computer and use it in GitHub Desktop.
Save techphoria414/c2c561b231f8c4f080ac80962085108f to your computer and use it in GitHub Desktop.
Rewrite interaction dates in Sitecore analytics database
$ErrorActionPreference = "Stop"
import-module mdbc
#
# Configuration Values
#
# The date you created the traffic which you wish to rewrite. Likely today.
$rewriteDate = Get-Date "9/8/2016"
# The start and end dates for the range of days to which the traffic should be rewritten.
$startDate = Get-Date "6/24/2016"
$endDate = Get-Date "9/7/2016"
# A string describing the distribution of visits over those days. Each integer is a 'slot' of days, higher value = more visits.
# The number of integers in this string must be divisible into the total number of days.
$trafficShape = "111233222311111"
#
# Functions
#
Function Adjust-DateTime($DateTime, $ToDate) {
return Get-Date -Year $ToDate.Year -Month $ToDate.Month -Day $ToDate.Day -Hour $DateTime.Hour -Minute $DateTime.Minute -Second $DateTime.Second
}
#
# THE SCRIPT
#
# Determine total days, slots, days per slot
$totalDays = ($endDate - $startDate).Days
$trafficSlots = $trafficShape.Length
if ($totalDays % $trafficSlots -ne 0) {
Write-Host "Total days $totalDays not evenly divisble by $trafficSlots. Total days must be evenly divisble by traffic shape slots."
Exit
}
$daysPerSlot = $totalDays/$trafficSlots
Write-Host "Days per traffic slot: $daysPerSlot"
# Connect to the mongo db and find all the visits from the rewrite day
Write-Host "Finding visits from $rewriteDate..."
Connect-Mdbc . ActiveCommerceDemo_analytics Interactions
$rewriteDateEnd = $rewriteDate + (New-TimeSpan -Hours 24)
$visits = Get-MdbcData (New-MdbcQuery -And (New-MdbcQuery StartDateTime -gte $rewriteDate), (New-MdbcQuery StartDateTime -lt $rewriteDateEnd))
$rewriteCount = $visits.Count
Write-Host "$rewriteCount visits found"
# Determine the number of visits that will be allocated for each "point" in the traffic shape
$trafficSlots = $trafficShape.ToCharArray() | % { [Int32]::Parse($_) }
$totalPoints = ($trafficSlots | Measure-Object -Sum).Sum
$orphanVisits = $rewriteCount % $totalPoints
$visitsPerPoint = ($rewriteCount-$orphanVisits) / $totalPoints
Write-Host "Will allocate $visitsPerPoint visits per point in traffic shape."
Write-Host "This will leave $orphanVisits visits, we will try to distribute them."
# Put all rewrite visits into a queue
$visitQueue = New-Object System.Collections.Queue
$visits | % { $visitQueue.Enqueue($_) }
# Loops through all the slots
$currentDate = $startDate
$slotCount = 1
foreach ($slot in $trafficSlots) {
# Determine the number of visits in this slot
$slotVisits = $slot * $visitsPerPoint
if ($orphanVisits -gt 0) {
$slotVisits++
$orphanVisits--
}
$slotOrphanVisits = $slotVisits % $daysPerSlot
$slotVisitsPerDay = ($slotVisits-$slotOrphanVisits) / $daysPerSlot
Write-Host "Slot $slotCount ($slot points) has $slotVisits visits over $daysPerSlot days"
Write-Host "That's $slotVisitsPerDay visits per day with $slotOrphanVisits visits left over. We'll try to distribute them too."
# Loop through the days in this slot
for ($i = 0; $i -lt $daysPerSlot; $i++) {
# Determine the number of visits on this day
$visitsThisDay = $slotVisitsPerDay
if ($slotOrphanVisits -gt 0) {
$visitsThisDay++
$slotOrphanVisits--
}
Write-Host "Moving $visitsThisDay to $currentDate"
# Loop through the visits on this day
for ($j = 0; $j -lt $visitsThisDay; $j++) {
# Grab a visit from the queue and adjust its dates
$visit = $visitQueue.Dequeue();
$visitStart = Adjust-DateTime -DateTime $visit.StartDateTime -ToDate $currentDate
$visitEnd = Adjust-DateTime -DateTime $visit.EndDateTime -ToDate $currentDate
$visitSave = Adjust-DateTime -DateTime $visit.SaveDateTime -ToDate $currentDate
foreach ($page in $visit.Pages) {
$page.DateTime = Adjust-DateTime -DateTime $page.DateTime -ToDate $currentDate
}
$visit._id | Update-MdbcData (New-MdbcUpdate @{
StartDateTime = $visitStart
EndDateTime = $visitEnd
SaveDateTime = $visitSave
Pages = $visit.Pages
})
}
$currentDate = $currentDate + (New-TimeSpan -Days 1)
}
$slotCount++
}
$leftoverVisits = $visitQueue.Count
Write-Host "There were $leftoverVisits visits left. This should be 0."
Days per traffic slot: 5
Finding visits from 09/08/2016 00:00:00...
2232 visits found
Will allocate 89 visits per point in traffic shape.
This will leave 7 visits, we will try to distribute them.
Slot 1 (1 points) has 90 visits over 5 days
That's 18 visits per day with 0 visits left over. We'll try to distribute them too.
Moving 18 to 06/24/2016 00:00:00
Moving 18 to 06/25/2016 00:00:00
Moving 18 to 06/26/2016 00:00:00
Moving 18 to 06/27/2016 00:00:00
Moving 18 to 06/28/2016 00:00:00
Slot 2 (1 points) has 90 visits over 5 days
That's 18 visits per day with 0 visits left over. We'll try to distribute them too.
Moving 18 to 06/29/2016 00:00:00
Moving 18 to 06/30/2016 00:00:00
Moving 18 to 07/01/2016 00:00:00
Moving 18 to 07/02/2016 00:00:00
Moving 18 to 07/03/2016 00:00:00
Slot 3 (1 points) has 90 visits over 5 days
That's 18 visits per day with 0 visits left over. We'll try to distribute them too.
Moving 18 to 07/04/2016 00:00:00
Moving 18 to 07/05/2016 00:00:00
Moving 18 to 07/06/2016 00:00:00
Moving 18 to 07/07/2016 00:00:00
Moving 18 to 07/08/2016 00:00:00
Slot 4 (2 points) has 179 visits over 5 days
That's 35 visits per day with 4 visits left over. We'll try to distribute them too.
Moving 36 to 07/09/2016 00:00:00
Moving 36 to 07/10/2016 00:00:00
Moving 36 to 07/11/2016 00:00:00
Moving 36 to 07/12/2016 00:00:00
Moving 35 to 07/13/2016 00:00:00
Slot 5 (3 points) has 268 visits over 5 days
That's 53 visits per day with 3 visits left over. We'll try to distribute them too.
Moving 54 to 07/14/2016 00:00:00
Moving 54 to 07/15/2016 00:00:00
Moving 54 to 07/16/2016 00:00:00
Moving 53 to 07/17/2016 00:00:00
Moving 53 to 07/18/2016 00:00:00
Slot 6 (3 points) has 268 visits over 5 days
That's 53 visits per day with 3 visits left over. We'll try to distribute them too.
Moving 54 to 07/19/2016 00:00:00
Moving 54 to 07/20/2016 00:00:00
Moving 54 to 07/21/2016 00:00:00
Moving 53 to 07/22/2016 00:00:00
Moving 53 to 07/23/2016 00:00:00
Slot 7 (2 points) has 179 visits over 5 days
That's 35 visits per day with 4 visits left over. We'll try to distribute them too.
Moving 36 to 07/24/2016 00:00:00
Moving 36 to 07/25/2016 00:00:00
Moving 36 to 07/26/2016 00:00:00
Moving 36 to 07/27/2016 00:00:00
Moving 35 to 07/28/2016 00:00:00
Slot 8 (2 points) has 178 visits over 5 days
That's 35 visits per day with 3 visits left over. We'll try to distribute them too.
Moving 36 to 07/29/2016 00:00:00
Moving 36 to 07/30/2016 00:00:00
Moving 36 to 07/31/2016 00:00:00
Moving 35 to 08/01/2016 00:00:00
Moving 35 to 08/02/2016 00:00:00
Slot 9 (2 points) has 178 visits over 5 days
That's 35 visits per day with 3 visits left over. We'll try to distribute them too.
Moving 36 to 08/03/2016 00:00:00
Moving 36 to 08/04/2016 00:00:00
Moving 36 to 08/05/2016 00:00:00
Moving 35 to 08/06/2016 00:00:00
Moving 35 to 08/07/2016 00:00:00
Slot 10 (3 points) has 267 visits over 5 days
That's 53 visits per day with 2 visits left over. We'll try to distribute them too.
Moving 54 to 08/08/2016 00:00:00
Moving 54 to 08/09/2016 00:00:00
Moving 53 to 08/10/2016 00:00:00
Moving 53 to 08/11/2016 00:00:00
Moving 53 to 08/12/2016 00:00:00
Slot 11 (1 points) has 89 visits over 5 days
That's 17 visits per day with 4 visits left over. We'll try to distribute them too.
Moving 18 to 08/13/2016 00:00:00
Moving 18 to 08/14/2016 00:00:00
Moving 18 to 08/15/2016 00:00:00
Moving 18 to 08/16/2016 00:00:00
Moving 17 to 08/17/2016 00:00:00
Slot 12 (1 points) has 89 visits over 5 days
That's 17 visits per day with 4 visits left over. We'll try to distribute them too.
Moving 18 to 08/18/2016 00:00:00
Moving 18 to 08/19/2016 00:00:00
Moving 18 to 08/20/2016 00:00:00
Moving 18 to 08/21/2016 00:00:00
Moving 17 to 08/22/2016 00:00:00
Slot 13 (1 points) has 89 visits over 5 days
That's 17 visits per day with 4 visits left over. We'll try to distribute them too.
Moving 18 to 08/23/2016 00:00:00
Moving 18 to 08/24/2016 00:00:00
Moving 18 to 08/25/2016 00:00:00
Moving 18 to 08/26/2016 00:00:00
Moving 17 to 08/27/2016 00:00:00
Slot 14 (1 points) has 89 visits over 5 days
That's 17 visits per day with 4 visits left over. We'll try to distribute them too.
Moving 18 to 08/28/2016 00:00:00
Moving 18 to 08/29/2016 00:00:00
Moving 18 to 08/30/2016 00:00:00
Moving 18 to 08/31/2016 00:00:00
Moving 17 to 09/01/2016 00:00:00
Slot 15 (1 points) has 89 visits over 5 days
That's 17 visits per day with 4 visits left over. We'll try to distribute them too.
Moving 18 to 09/02/2016 00:00:00
Moving 18 to 09/03/2016 00:00:00
Moving 18 to 09/04/2016 00:00:00
Moving 18 to 09/05/2016 00:00:00
Moving 17 to 09/06/2016 00:00:00
There were 0 visits left. This should be 0.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment