Salesforce with date stamp
Anyone has any experience visualizing salesforce data with date stamp? I am trying to see how our total # of leads is progressing for each month. It seems to me that I have to join the salesforce data with domo calendar to do so.
Basically, I need to be able to see what was the # of active leads in any given month in the past and see the trend from month to month. It is like a running total, taken into consideration additional new leads minus lost leads in the month.
I tried joining the dataset with domo calendar and the number just does not add up. I am not sure what I did wrong. Here's the sql code I used to join the two datasets:
FROM domo_calendar AS d
INNER JOIN `advanced_salesforce_data` AS h
ON CASE WHEN h.`CreatedDate` > h.`CloseDate` THEN d.dt >= h.`CreatedDate` ELSE (d.dt >= h.`CreatedDate` AND d.dt <= IfNull(h.`CloseDate`, CURRENT_DATE())) END
WHERE d.dt >= (SELECT MIN(`CreatedDate`) FROM `advanced_salesforce_data`)
AND d.dt <= CURRENT_DATE()
ORDER BY d.dt
Any help would be appreciated.
I think your best bet is to convert your timestamp to just date form and use that to join your two datasets.
In a mySQL data flow you can try:
select h.*, d.`dt` from `advanced_salesforce_data` h
on date_format(d.`dt`,'%m-%d-%Y') = date_format(h.`CreatedDate`,'%m-%d-%Y')
If you're using Redshift, you can use:
select sh.*, d."dt" from "advanced_salesforce_data" h
on cdd."dt" = trunc(h."CreatedDate")
As a side point, I just worked through something similar looking at our salesforce data and looking at what was open in past months. Because salesforce doesn't have a robust audit history, I had to do some ETL work to create the full opportunity histories (created date and close date were insufficient to tell if something had been active or not since our close date field is manual entry and not systematically tied to stages) - this may be something to consider if you haven't dug into your salesforce data yet.0
So much to chew on here.
1) don't JOIN on a CASE. Implement your date calculations as separate columns so it's easier to see what you're doing and troubleshoot. It's Salesforce and SQL, performance 'does not matter', so the time to generate an extra table is negligible. (or you could use ALTER and UPDATE commands if you'd rather do in place transforms.
Logic like "h.`CreatedDate` > h.`CloseDate` " could be an 'isIndclude' flag so you can see the results. Also, as @mhouston you're probably not building a comprehensive history if you're doing daily replace. Consider Recursive Queries or an incremental load strategy.
IfNull(h.`CloseDate`, CURRENT_DATE())) should be a materialized column so that you have this value in your cards. Think about it from the perspective of the end user. I don't want to have to troll through code if I disagree with your filter / output dataset. You could easily include a 'CloseDate_Adjusted' column.
If you do all the preprocessing, your goal should be a simple JOIN on BETWEEN and no WHERE clause.
Do have a date and dateTime column. For your analysis, the time element seems superfluous, and from the visualization perspective, you'll have better performance in analyzer with a lower cardinality dateOnly column.
@mhouston is right, you'll have difficulty talking about the change in pipeline if you don't start accumulating history. for projects I've worked on, I set up a pipeline where each row of data represents a daily snapshot of opportunities that changed. This is the easiest way to build a dataset that allows you to analyze the change in your pipeline day by day or month over month.Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
- 7.7K All Categories
- 12 Connect
- 925 Connectors
- 247 Workbench
- 441 Transform
- 1.7K Magic ETL
- 61 SQL DataFlows
- 456 Datasets
- 88 Visualize
- 222 Beast Mode
- 2.1K Charting
- 8 Variables
- 35 Cards, Dashboards, Stories
- 5 Automate
- 349 APIs & Domo Developer
- 85 Apps
- 17 Predict
- 3 Jupyter Workspaces
- 14 R & Python Tiles
- 242 Distribute
- 60 Domo Everywhere
- 241 Scheduled Reports
- 18 Manage
- 39 Governance & Security
- 46 Product Ideas
- 1.1K Ideas Exchange
- 6 Community Forums
- 19 Getting Started
- 6 Community Member Introductions
- 50 Community News
- 18 Event Recordings
- 577 日本支部