Rewrite interaction dates in Sitecore analytics database

rewrite-dates.ps1

$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."

zz-example-output.txt

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.