Forum Discussion
Separating MailChimp data by month?
I submit data reports on a monthly basis and I've found it to be quite difficult to separate data by month. Hopefully this is just something simple I'm missing.
When I connect PBI to MailChimp, I'm provided with a few different datasets. Unlike Google Analytics for example where I can mix and match whatever data I want, I can't combine measures from different datasets - you literally just get what is given to you.
The 'Campaigns' dataset gives me a send date column which I was able to split and breakdown into months**.
However, although the 'List' dataset gives me a ton of great info, there isn't a single column to split/separate by month. Is there any way to do this?
**Bonus question: when I split the send date column (originally was a day/month/year format) into different columns, is PBI still able to refresh new data from MailChimp or will it not understand what the new columns are?
Thanks!
7 Replies
Not sure about mailchimp but would suggest not splitting send date, instead you can have a seperate date table that contains all date columns you need which is linked by send date.
PowerBi should remember the split steps on re-import but you won't need to do that if you have a date table like above.
Have you tried the mailchimp content pack?
https://powerbi.microsoft.com/en-us/documentation/powerbi-content-pack-mailchimp/
Thanks, I'll work on using a table for the campaign information. It still doesn't help with the list data though, unfortunately.
I've used that content pack on the web version and while it gives me most of what I need it doesn't give me everything + that pack isn't the same when using PBI desktop. Web version has some awesome data I can use and so does desktop but each one has holes that the other could fill. I guess it's to be expected though seeing as how the MailChimp pack is still in beta.
- I'll have a look at the mailchimp table structures when I get into work tomorrow, bet someone beats me to it though, lots of knowledgeable folks on here
jessem - Im not sure I completely understand your question but I will answer the best I can. If you want to split the dates by month you can try this option (its what I do). In excel create a table with dates you need, for example from 01/01/2014 - 31/12/2015. In the column next to it copy and paste the same information again but change the formatting to be custom/type M. This will give you the month as a number. Repeat this step again in another column and change formatting to be custom/type MMM. This will give you the month like this Jan, Feb etc.
Now with the new create table feature in power BI you can copy this data into PBI. The reason you need a column with the number of the month is so you can sort the column with months as text (Jan, Feb, etc). Click on the column with the worded months and then click the sort by key in the ribbon, and sort my number month. Now the worded months will always be in the correct order.
Once this is done create a relationship between you MailChimp data and the date table through the dates. Now you will be able to filter your data by month.
I hope this helps. It takes a bit of prework, but once it is done it makes sorting by month easy. You can then start adding in other date fields such as financial year/week/month, quarter, etc.
Hello, now that the Mailchimp service pack was deprecated and that direct connector is not available anymore, you would have to look for another way. As a workaround, maybe you can use a 3rd party connector, it can save you a lot of time compared to other options. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. It pulls data through the Mailchimp API and has a date and month field, so it will be pretty easy for you to separate data by month. In case you wonder, to make the connection first search for the Mailchimp connector in the data sources list:
After that, you will have to first create a secret API key in your Mailchimp account and then using your credentials add your account to the platform.
Once you’ve done this, you can proceed and go to the “Preview and destination” step
There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.
