Multi-Location Marketing
Every dealership got its own monthly report, because the naming was right on day one.
A dealer group ran marketing campaigns for more than a thousand locations and needed every location to see how its own campaigns performed. The campaign naming convention was settled before the first campaign launched, so one report template covered every location and a master rollup covered the group. Three years of new campaigns landed in the same reporting with no rework.
The problem
The group wanted marketing campaigns for every location, and wanted every location to see how its own campaigns performed. At that count the reporting is the hard part, not the campaigns.
Build the reports one location at a time and you are building a thousand reports, then maintaining a thousand reports. Every new campaign launch means going back and wiring it into the right one. Miss a location and someone at that store is looking at a blank page, or worse, at another store's numbers.
This only works if a campaign can be traced back to its location automatically. No lookup table for someone to maintain by hand, no analyst deciding where an ad belongs.
The work
Campaigns ran across three marketing channels and seven ad platforms. About twelve people were launching them. That headcount is usually where a naming convention starts to drift, because it stops being one person's habit and becomes twelve people's memory.
It held because nobody had to remember it. The people launching campaigns entered their campaign naming data into a defined intake, and that step did the sorting. It assembled the name and wrote the dealership ID into a fixed slot, the third position in the campaign name. Same slot on every platform, every channel, every launch.
That fixed position is what made the parsing trivial on the other end. Ad platform APIs fed campaign performance into BigQuery, and first-party sales data from the company landed alongside it. dbt read the campaign name, pulled the dealership ID out of the third position, and added the derived fields to the final data table: location, channel, platform, and tactic. Every row arrived already attributed to a rooftop.
Twelve people entering campaign data across seven platforms, and no reconciliation step anywhere in the flow. The convention did the job a mapping table would otherwise have done, and it did it at launch instead of after the fact.
| channel | tactic | dealership id | promo |
|---|---|---|---|
| search_ | brand_ | 1042_ | summerpromo |
| social_ | prospecting_ | 0817_ | tradein |
| display_ | retargeting_ | 2291_ | certified |
| video_ | awareness_ | 1042_ | newmodel |
| search_ | service_ | 0817_ | oilchange |
-- position 3 is the dealership id, on every platform
with parsed as (
select
campaign_name,
split(campaign_name, '_')[safe_offset(0)] as channel,
split(campaign_name, '_')[safe_offset(1)] as tactic,
split(campaign_name, '_')[safe_offset(2)] as dealership_id,
split(campaign_name, '_')[safe_offset(3)] as promo,
spend, impressions, clicks, conversions
from {{ ref('stg_ad_platforms__campaigns') }}
)
select
parsed.*,
dealers.dealership_name,
dealers.region,
sales.units_sold
from parsed
left join {{ ref('dim_dealerships') }} as dealers
on parsed.dealership_id = dealers.dealership_id
left join {{ ref('fct_dealer_sales') }} as sales
on parsed.dealership_id = sales.dealership_id
The solution
We settled the naming convention before the first campaign went live. That is the only reason everything after it was cheap.
Reporting became one template instead of a thousand builds. The Google Data Studio report was parameterized on location, so each rooftop opened its own view of its own spend and performance, and over a thousand tailored reports went out every month without anyone assembling them. Because the sales data came in next to the campaign data, those reports showed what a location spent against what it actually sold.
A master report sat on the same models and gave the group the rollup across every location, including the ability to compare rooftops against each other.
The part that paid off over time was what happened to new campaigns. A campaign launched in year three, named to the same convention, showed up in the correct location report and in the master rollup without anyone touching the reporting layer. Three years of launches ran through it. The convention held, so the reporting never needed a rebuild.
Results
- Over 1,000 tailored dealer reports went out every month, all served from a single parameterized template
- Campaign-to-location mapping came out of the campaign name itself, with no manual lookup table to maintain
- Twelve people launching across three channels and seven platforms produced consistently parseable campaign names
- A master rollup gave the group cross-location comparison from the same models feeding the individual reports
- Three years of new campaign launches landed in the right reports with no rework to the reporting layer
“We have not had reporting like this to show our locations before! It's great to see by location where marketing is performing well, and where the ad spend is going.”
Freelance analytics engineer. Ten years across dbt, BigQuery, Snowflake and Databricks.
If your stack looks anything like this, let’s talk.
Engagements run four to eight weeks and land as pull requests against your analytics repo. One person, one email, no account team.